using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using WegasManagementSystem.Models;

namespace WegasManagementSystem.DAL
{
    /// <summary>
    /// 员工数据访问类
    /// </summary>
    public class EmployeeDAL
    {
        /// <summary>
        /// 添加员工
        /// </summary>
        /// <param name="employee">员工对象</param>
        /// <returns>新员工ID，失败返回0</returns>
        public int AddEmployee(Employee employee)
        {
            string sql = @"INSERT INTO emp_employee (emp_number, name, gender, dept_id, position, title, base_salary, created_time, updated_time) 
                          VALUES (@emp_number, @name, @gender, @dept_id, @position, @title, @base_salary, @created_time, @updated_time);
                          SELECT LAST_INSERT_ID();";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_number", employee.EmpNumber),
                new MySqlParameter("@name", employee.Name),
                new MySqlParameter("@gender", employee.Gender),
                new MySqlParameter("@dept_id", employee.DeptId),
                new MySqlParameter("@position", employee.Position),
                new MySqlParameter("@title", employee.Title),
                new MySqlParameter("@base_salary", employee.BaseSalary),
                new MySqlParameter("@created_time", employee.CreatedTime),
                new MySqlParameter("@updated_time", employee.UpdatedTime)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null ? Convert.ToInt32(result) : 0;
        }

        /// <summary>
        /// 更新员工信息
        /// </summary>
        /// <param name="employee">员工对象</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateEmployee(Employee employee)
        {
            string sql = @"UPDATE emp_employee 
                          SET emp_number = @emp_number, name = @name, gender = @gender, 
                              dept_id = @dept_id, position = @position, title = @title, 
                              base_salary = @base_salary, updated_time = @updated_time
                          WHERE emp_id = @emp_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", employee.EmpId),
                new MySqlParameter("@emp_number", employee.EmpNumber),
                new MySqlParameter("@name", employee.Name),
                new MySqlParameter("@gender", employee.Gender),
                new MySqlParameter("@dept_id", employee.DeptId),
                new MySqlParameter("@position", employee.Position),
                new MySqlParameter("@title", employee.Title),
                new MySqlParameter("@base_salary", employee.BaseSalary),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除员工
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <returns>删除成功返回true，失败返回false</returns>
        public bool DeleteEmployee(int empId)
        {
            // 检查是否有用户关联了该员工
            string checkSql = "SELECT COUNT(*) FROM sys_user WHERE emp_id = @emp_id";
            MySqlParameter checkParam = new MySqlParameter("@emp_id", empId);
            object checkResult = DBConnection.ExecuteScalar(checkSql, CommandType.Text, checkParam);
            
            if (checkResult != null && Convert.ToInt32(checkResult) > 0)
            {
                throw new Exception("该员工已关联用户账号，请先删除关联的用户！");
            }

            // 检查是否有工资记录
            string checkSalaryRecordSql = "SELECT COUNT(*) FROM sal_salary_record WHERE emp_id = @emp_id";
            object checkSalaryResult = DBConnection.ExecuteScalar(checkSalaryRecordSql, CommandType.Text, checkParam);
            
            if (checkSalaryResult != null && Convert.ToInt32(checkSalaryResult) > 0)
            {
                throw new Exception("该员工有工资记录，无法删除！");
            }

            string sql = "DELETE FROM emp_employee WHERE emp_id = @emp_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 获取所有员工
        /// </summary>
        /// <returns>员工列表</returns>
        public List<Employee> GetAllEmployees()
        {
            string sql = @"SELECT e.*, d.dept_name 
                          FROM emp_employee e 
                          LEFT JOIN org_department d ON e.dept_id = d.dept_id
                          ORDER BY e.emp_id";

            DataTable dt = DBConnection.ExecuteDataTable(sql);
            List<Employee> employees = new List<Employee>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    employees.Add(MapRowToEmployee(row));
                }
            }

            return employees;
        }

        /// <summary>
        /// 根据部门ID获取员工
        /// </summary>
        /// <param name="deptId">部门ID</param>
        /// <returns>员工列表</returns>
        public List<Employee> GetEmployeesByDepartment(int deptId)
        {
            string sql = @"SELECT e.*, d.dept_name 
                          FROM emp_employee e 
                          LEFT JOIN org_department d ON e.dept_id = d.dept_id
                          WHERE e.dept_id = @dept_id
                          ORDER BY e.emp_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_id", deptId)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            List<Employee> employees = new List<Employee>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    employees.Add(MapRowToEmployee(row));
                }
            }

            return employees;
        }

        /// <summary>
        /// 根据ID获取员工
        /// </summary>
        /// <param name="empId">员工ID</param>
        /// <returns>员工对象</returns>
        public Employee GetEmployeeById(int empId)
        {
            string sql = @"SELECT e.*, d.dept_name 
                          FROM emp_employee e 
                          LEFT JOIN org_department d ON e.dept_id = d.dept_id
                          WHERE e.emp_id = @emp_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_id", empId)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToEmployee(dt.Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 检查工号是否存在
        /// </summary>
        /// <param name="empNumber">工号</param>
        /// <param name="excludeEmpId">排除的员工ID</param>
        /// <returns>存在返回true，不存在返回false</returns>
        public bool CheckEmpNumberExists(string empNumber, int excludeEmpId = 0)
        {
            string sql = "SELECT COUNT(*) FROM emp_employee WHERE emp_number = @emp_number";
            List<MySqlParameter> parameters = new List<MySqlParameter>
            {
                new MySqlParameter("@emp_number", empNumber)
            };

            if (excludeEmpId > 0)
            {
                sql += " AND emp_id <> @emp_id";
                parameters.Add(new MySqlParameter("@emp_id", excludeEmpId));
            }

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters.ToArray());
            return result != null && Convert.ToInt32(result) > 0;
        }

        /// <summary>
        /// 根据员工工号获取员工信息
        /// </summary>
        /// <param name="empNumber">员工工号</param>
        /// <returns>员工信息对象</returns>
        public Employee GetEmployeeByNumber(string empNumber)
        {
            if (string.IsNullOrEmpty(empNumber))
            {
                return null;
            }

            string sql = "SELECT e.*, d.dept_name FROM emp_employee e LEFT JOIN org_department d ON e.dept_id = d.dept_id WHERE e.emp_number = @emp_number";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp_number", empNumber)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToEmployee(dt.Rows[0]);
            }
            
            return null;
        }

        /// <summary>
        /// 将DataRow映射到Employee对象
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <returns>Employee对象</returns>
        private Employee MapRowToEmployee(DataRow row)
        {
            Employee employee = new Employee();
            employee.EmpId = Convert.ToInt32(row["emp_id"]);
            employee.EmpNumber = row["emp_number"].ToString();
            employee.Name = row["name"].ToString();
            employee.Gender = Convert.ToInt32(row["gender"]);
            employee.DeptId = Convert.ToInt32(row["dept_id"]);
            employee.Position = row["position"].ToString();
            employee.Title = row["title"].ToString();
            employee.BaseSalary = Convert.ToDecimal(row["base_salary"]);
            employee.CreatedTime = Convert.ToDateTime(row["created_time"]);
            employee.UpdatedTime = Convert.ToDateTime(row["updated_time"]);

            // 可能是连接查询的额外字段
            if (row.Table.Columns.Contains("dept_name") && row["dept_name"] != DBNull.Value)
                employee.DeptName = row["dept_name"].ToString();

            return employee;
        }
    }
} 